package org.example.mock;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;

public class MySQLMockDataGenerator {
    // Database connection configuration
    private static final String DB_URL = "jdbc:mysql://hadoop102/bookstore?useSSL=false&requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "000000";

    // Date range and record count
    private LocalDate startDate;
    private LocalDate endDate;
    private int recordCount;

    // ID lists for foreign key relationships
    private final List<String> bookIds = new ArrayList<>();
    private final List<String> storeIds = new ArrayList<>();
    private final List<String> memberIds = new ArrayList<>();
    private final List<String> categoryIds = new ArrayList<>();
    private final Random random = new Random();

    // Constructor
    public MySQLMockDataGenerator(LocalDate startDate, LocalDate endDate, int recordCount) {
        this.startDate = startDate;
        this.endDate = endDate;
        this.recordCount = recordCount;
    }

    public static void main(String[] args) {
        try {
            // Set date range (past 365 days) and record count
            LocalDate endDate = LocalDate.now();
            LocalDate startDate = endDate.minusDays(365);
            int recordCount = 100; // Base record count for each table

            MySQLMockDataGenerator generator = new MySQLMockDataGenerator(startDate, endDate, recordCount);
            generator.generateAllData();
            System.out.println("Bookstore data generation completed!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Generate all data in dependency order
    public void generateAllData() throws SQLException {
        // Clear existing data (optional)
        clearAllTables();

        // Generate base data
        generateCategories();
        generateBooks();
        generateStores();
        generateMembers();

        // Generate dependent data
        generateInventory();
        generateSalesRecords();
        generateStoreTraffic();
    }

    // Clear all tables
    private void clearAllTables() throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            // Disable foreign key checks
            stmt.execute("SET FOREIGN_KEY_CHECKS = 0");

            // Clear tables in reverse dependency order
            stmt.execute("TRUNCATE TABLE store_traffic");
            stmt.execute("TRUNCATE TABLE sales_record");
            stmt.execute("TRUNCATE TABLE inventory");
            stmt.execute("TRUNCATE TABLE member_info");
            stmt.execute("TRUNCATE TABLE store_info");
            stmt.execute("TRUNCATE TABLE book_info");

            // Re-enable foreign key checks
            stmt.execute("SET FOREIGN_KEY_CHECKS = 1");
        }
    }

    // Get database connection
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    // Generate random date within range
    private LocalDate randomDate() {
        long startEpochDay = startDate.toEpochDay();
        long endEpochDay = endDate.toEpochDay();
        long randomDay = ThreadLocalRandom.current().nextLong(startEpochDay, endEpochDay);
        return LocalDate.ofEpochDay(randomDay);
    }

    // Generate random date-time within range
    private LocalDateTime randomDateTime() {
        LocalDate randomDate = randomDate();
        int hour = random.nextInt(24);
        int minute = random.nextInt(60);
        int second = random.nextInt(60);
        return LocalDateTime.of(randomDate, LocalTime.of(hour, minute, second));
    }

    // Generate random decimal
    private BigDecimal randomBigDecimal(double min, double max, int scale) {
        double value = min + (max - min) * random.nextDouble();
        return BigDecimal.valueOf(value).setScale(scale, BigDecimal.ROUND_HALF_UP);
    }

    // Random element from list
    private <T> T randomFromList(List<T> list) {
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(random.nextInt(list.size()));
    }

    // Generate book categories
    private void generateCategories() throws SQLException {
        String sql = "INSERT INTO book_category (category_id, category_name) VALUES (?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            List<String[]> categories = Arrays.asList(
                    new String[]{"FIC", "Fiction"},
                    new String[]{"NON", "Non-Fiction"},
                    new String[]{"SCI", "Science"},
                    new String[]{"BIO", "Biography"},
                    new String[]{"HIS", "History"},
                    new String[]{"ART", "Art & Photography"},
                    new String[]{"CHI", "Children's"},
                    new String[]{"TEC", "Technology"},
                    new String[]{"BUS", "Business"},
                    new String[]{"COO", "Cookbooks"}
            );

            for (String[] category : categories) {
                pstmt.setString(1, category[0]);
                pstmt.setString(2, category[1]);
                pstmt.executeUpdate();
                categoryIds.add(category[0]);
            }
        }
    }

    // Generate book data
    private void generateBooks() throws SQLException {
        String sql = "INSERT INTO book_info (book_id, book_name, author, publisher, publish_date, " +
                "category_id, category_name, price, cost, isbn, page_count, word_count, " +
                "print_time, stock_status, create_time, update_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            List<String> firstWords = Arrays.asList("The", "A", "My", "Your", "Our", "Their", "His", "Her");
            List<String> nouns = Arrays.asList("Book", "Story", "Journey", "Adventure", "Secret", "Life", "Time", "World");
            List<String> adjectives = Arrays.asList("Great", "Amazing", "Hidden", "Lost", "Forgotten", "Beautiful", "Dark", "Mysterious");
            List<String> authors = Arrays.asList("John Smith", "Emily Johnson", "Michael Brown", "Sarah Davis", "Robert Wilson",
                    "Jennifer Lee", "David Miller", "Lisa Anderson");
            List<String> publishers = Arrays.asList("Penguin", "HarperCollins", "Random House", "Simon & Schuster", "Macmillan");

            for (int i = 0; i < recordCount; i++) {
                String bookId = "BK" + String.format("%05d", i);
                String bookName = randomFromList(firstWords) + " " + randomFromList(adjectives) + " " + randomFromList(nouns);
                String author = randomFromList(authors);
                String publisher = randomFromList(publishers);
                LocalDate publishDate = randomDate().minusDays(random.nextInt(365 * 5)); // Books published up to 5 years ago
                String categoryId = randomFromList(categoryIds);
                String categoryName = getCategoryName(categoryId);
                BigDecimal price = randomBigDecimal(10, 50, 2);
                BigDecimal cost = price.multiply(randomBigDecimal(0.4, 0.7, 2));
                String isbn = generateISBN();
                int pageCount = 100 + random.nextInt(500);
                int wordCount = pageCount * (250 + random.nextInt(250));
                int printTime = 1 + random.nextInt(10);
                int stockStatus = random.nextInt(4); // 0-3
                LocalDateTime createTime = randomDateTime();
                LocalDateTime updateTime = createTime.plusDays(random.nextInt(30));

                pstmt.setString(1, bookId);
                pstmt.setString(2, bookName);
                pstmt.setString(3, author);
                pstmt.setString(4, publisher);
                pstmt.setDate(5, Date.valueOf(publishDate));
                pstmt.setString(6, categoryId);
                pstmt.setString(7, categoryName);
                pstmt.setBigDecimal(8, price);
                pstmt.setBigDecimal(9, cost);
                pstmt.setString(10, isbn);
                pstmt.setInt(11, pageCount);
                pstmt.setInt(12, wordCount);
                pstmt.setInt(13, printTime);
                pstmt.setInt(14, stockStatus);
                pstmt.setTimestamp(15, Timestamp.valueOf(createTime));
                pstmt.setTimestamp(16, Timestamp.valueOf(updateTime));

                pstmt.executeUpdate();
                bookIds.add(bookId);
            }
        }
    }

    // Get category name by ID
    private String getCategoryName(String categoryId) throws SQLException {
        String sql = "SELECT category_name FROM book_category WHERE category_id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, categoryId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("category_name");
                }
            }
        }
        return "Unknown";
    }

    // Generate ISBN
    private String generateISBN() {
        // Simple ISBN-13 format
        return "978" + (random.nextInt(900000000) + 100000000) + random.nextInt(10);
    }

    // Generate store data
    private void generateStores() throws SQLException {
        String sql = "INSERT INTO store_info (store_id, store_name, province, city, district, address, " +
                "area, open_date, manager, employee_count, contact_phone, status, create_time, update_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            List<String> provinces = Arrays.asList("Beijing", "Shanghai", "Guangdong", "Jiangsu", "Zhejiang");
            List<String> cities = Arrays.asList("Beijing", "Shanghai", "Guangzhou", "Shenzhen", "Nanjing", "Hangzhou");
            List<String> districts = Arrays.asList("Xicheng", "Dongcheng", "Pudong", "Xuhui", "Yuexiu", "Jianggan");
            List<String> streetNames = Arrays.asList("Main", "Central", "Park", "Oak", "Pine", "Maple", "Elm");
            List<String> managers = Arrays.asList("Zhang Wei", "Li Na", "Wang Fang", "Liu Yang", "Chen Hao");

            for (int i = 0; i < recordCount / 10; i++) { // Generate fewer stores
                String storeId = "ST" + String.format("%03d", i);
                String storeName = "Bookstore " + (i + 1);
                String province = randomFromList(provinces);
                String city = randomFromList(cities);
                String district = randomFromList(districts);
                String address = random.nextInt(100) + " " + randomFromList(streetNames) + " Road";
                BigDecimal area = randomBigDecimal(50, 300, 2);
                LocalDate openDate = randomDate().minusDays(random.nextInt(365 * 3)); // Stores opened up to 3 years ago
                String manager = randomFromList(managers);
                int employeeCount = 5 + random.nextInt(15);
                String contactPhone = "1" + String.format("%010d", random.nextInt(1000000000));
                int status = random.nextDouble() < 0.9 ? 0 : (random.nextBoolean() ? 1 : 2); // Mostly normal status
                LocalDateTime createTime = randomDateTime();
                LocalDateTime updateTime = createTime.plusDays(random.nextInt(30));

                pstmt.setString(1, storeId);
                pstmt.setString(2, storeName);
                pstmt.setString(3, province);
                pstmt.setString(4, city);
                pstmt.setString(5, district);
                pstmt.setString(6, address);
                pstmt.setBigDecimal(7, area);
                pstmt.setDate(8, Date.valueOf(openDate));
                pstmt.setString(9, manager);
                pstmt.setInt(10, employeeCount);
                pstmt.setString(11, contactPhone);
                pstmt.setInt(12, status);
                pstmt.setTimestamp(13, Timestamp.valueOf(createTime));
                pstmt.setTimestamp(14, Timestamp.valueOf(updateTime));

                pstmt.executeUpdate();
                storeIds.add(storeId);
            }
        }
    }

    // Generate member data
    private void generateMembers() throws SQLException {
        String sql = "INSERT INTO member_info (member_id, member_name, gender, birth_date, phone, email, " +
                "register_date, register_store, member_level, total_points, available_points, " +
                "last_consume_date, create_time, update_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            List<String> firstNames = Arrays.asList("Zhang", "Wang", "Li", "Liu", "Chen", "Yang", "Zhao", "Huang");
            List<String> lastNames = Arrays.asList("Wei", "Na", "Fang", "Yang", "Hao", "Lei", "Min", "Jie");
            List<String> emailDomains = Arrays.asList("gmail.com", "yahoo.com", "hotmail.com", "outlook.com");

            for (int i = 0; i < recordCount * 2; i++) { // Generate more members
                String memberId = "MB" + String.format("%05d", i);
                String memberName = randomFromList(firstNames) + " " + randomFromList(lastNames);
                int gender = random.nextInt(3); // 0-2
                LocalDate birthDate = randomDate().minusYears(18 + random.nextInt(50)); // Adults 18-68
                String phone = "1" + String.format("%010d", random.nextInt(1000000000));
                String email = (memberName.replace(" ", ".") + "@" + randomFromList(emailDomains)).toLowerCase();
                LocalDate registerDate = randomDate();
                String registerStore = randomFromList(storeIds);
                int memberLevel = random.nextInt(4) + 1; // 1-4
                int totalPoints = random.nextInt(1000);
                int availablePoints = Math.min(totalPoints, random.nextInt(800));
                LocalDate lastConsumeDate = random.nextBoolean() ? registerDate.plusDays(random.nextInt(365)) : null;
                LocalDateTime createTime = randomDateTime();
                LocalDateTime updateTime = createTime.plusDays(random.nextInt(30));

                pstmt.setString(1, memberId);
                pstmt.setString(2, memberName);
                pstmt.setInt(3, gender);
                pstmt.setDate(4, birthDate != null ? Date.valueOf(birthDate) : null);
                pstmt.setString(5, phone);
                pstmt.setString(6, email);
                pstmt.setDate(7, Date.valueOf(registerDate));
                pstmt.setString(8, registerStore);
                pstmt.setInt(9, memberLevel);
                pstmt.setInt(10, totalPoints);
                pstmt.setInt(11, availablePoints);
                pstmt.setDate(12, lastConsumeDate != null ? Date.valueOf(lastConsumeDate) : null);
                pstmt.setTimestamp(13, Timestamp.valueOf(createTime));
                pstmt.setTimestamp(14, Timestamp.valueOf(updateTime));

                pstmt.executeUpdate();
                memberIds.add(memberId);
            }
        }
    }

    // Generate inventory data
    private void generateInventory() throws SQLException {
        String sql = "INSERT INTO inventory (store_id, book_id, current_stock, safety_stock, " +
                "last_check_date, last_delivery_date, create_time, update_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // For each store, create inventory for a subset of books
            for (String storeId : storeIds) {
                // Randomly select 30-70% of books to have inventory in this store
                int booksPerStore = (int) (bookIds.size() * (0.3 + 0.4 * random.nextDouble()));
                Collections.shuffle(bookIds);

                for (int i = 0; i < booksPerStore; i++) {
                    String bookId = bookIds.get(i);
                    int currentStock = random.nextInt(50);
                    int safetyStock = (int) (currentStock * (0.3 + 0.3 * random.nextDouble()));
                    LocalDate lastCheckDate = randomDate();
                    LocalDate lastDeliveryDate = lastCheckDate.minusDays(random.nextInt(30));
                    LocalDateTime createTime = randomDateTime();
                    LocalDateTime updateTime = createTime.plusDays(random.nextInt(30));

                    pstmt.setString(1, storeId);
                    pstmt.setString(2, bookId);
                    pstmt.setInt(3, currentStock);
                    pstmt.setInt(4, safetyStock);
                    pstmt.setDate(5, Date.valueOf(lastCheckDate));
                    pstmt.setDate(6, Date.valueOf(lastDeliveryDate));
                    pstmt.setTimestamp(7, Timestamp.valueOf(createTime));
                    pstmt.setTimestamp(8, Timestamp.valueOf(updateTime));

                    pstmt.executeUpdate();
                }
            }
        }
    }

    // Generate sales records
    private void generateSalesRecords() throws SQLException {
        String sql = "INSERT INTO sales_record (order_id, store_id, book_id, sale_date, sale_time, " +
                "quantity, unit_price, total_amount, payment_method, member_id, employee_id, create_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < recordCount * 5; i++) { // Generate more sales records
                String orderId = "ORD" + System.currentTimeMillis() + random.nextInt(1000);
                String storeId = randomFromList(storeIds);
                String bookId = randomFromList(bookIds);
                LocalDate saleDate = randomDate();
                LocalTime saleTime = LocalTime.of(9 + random.nextInt(10), random.nextInt(60)); // 9am-7pm
                int quantity = 1 + random.nextInt(3); // 1-3 copies
                BigDecimal unitPrice = getBookPrice(bookId);
                BigDecimal totalAmount = unitPrice.multiply(BigDecimal.valueOf(quantity));
                int paymentMethod = 1 + random.nextInt(4); // 1-4
                String memberId = random.nextDouble() < 0.7 ? randomFromList(memberIds) : null; // 70% chance member purchase
                String employeeId = "EMP" + String.format("%03d", random.nextInt(20)); // Random employee
                LocalDateTime createTime = LocalDateTime.of(saleDate, saleTime);

                pstmt.setString(1, orderId);
                pstmt.setString(2, storeId);
                pstmt.setString(3, bookId);
                pstmt.setDate(4, Date.valueOf(saleDate));
                pstmt.setTime(5, Time.valueOf(saleTime));
                pstmt.setInt(6, quantity);
                pstmt.setBigDecimal(7, unitPrice);
                pstmt.setBigDecimal(8, totalAmount);
                pstmt.setInt(9, paymentMethod);
                pstmt.setString(10, memberId);
                pstmt.setString(11, employeeId);
                pstmt.setTimestamp(12, Timestamp.valueOf(createTime));

                pstmt.executeUpdate();
            }
        }
    }

    // Get book price
    private BigDecimal getBookPrice(String bookId) throws SQLException {
        String sql = "SELECT price FROM book_info WHERE book_id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBigDecimal("price");
                }
            }
        }
        return BigDecimal.valueOf(29.99); // Default price
    }

    // Generate store traffic data
    private void generateStoreTraffic() throws SQLException {
        String sql = "INSERT INTO store_traffic (store_id, record_date, hour_of_day, customer_count, " +
                "stay_time_avg, conversion_rate, create_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Generate daily traffic for each store for the past 30 days
            for (String storeId : storeIds) {
                LocalDate currentDate = endDate.minusDays(30);

                while (!currentDate.isAfter(endDate)) {
                    // Generate hourly traffic for business hours (10am-8pm)
                    for (int hour = 10; hour <= 20; hour++) {
                        int customerCount = 5 + random.nextInt(20); // 5-25 customers per hour
                        BigDecimal stayTimeAvg = randomBigDecimal(5, 30, 1); // 5-30 minutes
                        BigDecimal conversionRate = randomBigDecimal(5, 30, 2); // 5-30%
                        LocalDateTime createTime = LocalDateTime.of(currentDate, LocalTime.of(hour, 0));

                        pstmt.setString(1, storeId);
                        pstmt.setDate(2, Date.valueOf(currentDate));
                        pstmt.setInt(3, hour);
                        pstmt.setInt(4, customerCount);
                        pstmt.setBigDecimal(5, stayTimeAvg);
                        pstmt.setBigDecimal(6, conversionRate);
                        pstmt.setTimestamp(7, Timestamp.valueOf(createTime));

                        pstmt.executeUpdate();
                    }
                    currentDate = currentDate.plusDays(1);
                }
            }
        }
    }
}